﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Collections.Generic;
using System.Globalization;

public partial class Report_OrderStatistics : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Check.CheckPermission("900900");
        if (!IsPostBack)
        {
            ORDER_OFCODE(); //加载订单来源
            Init_zdr();     //制单人
            ORDER_DTCODE(); //交易类型
            InitPayType();  //支付方式
            Init_ServiceSite(); //服务站初始化
            Init_Suggestion();//订单意见初始化
            Tbx_CustomerCode.Attributes.Add("onclick", "MultiSearchVIP('../');");
            Session["FCode"] = "900900";
        }
    }

    #region 订单来源
    private void ORDER_OFCODE()
    {/*
        List<Model.ORDERFROM> properties = new BLL.ORDERFROM().GetAllList();
        DDL_OrderFrom.DataSource = properties;
        DDL_OrderFrom.DataTextField = "OF_NAME";
        DDL_OrderFrom.DataValueField = "OF_CODE";
        DDL_OrderFrom.DataBind();
        ListItem li = new ListItem();
        li.Text = "全部";
        li.Value = "-1";
        DDL_OrderFrom.Items.Insert(0, li);*/
    }
    #endregion

    #region 制单人/订单审核人/财务审核人/发货人/结算人初始化
    private void Init_zdr()
    {
        List<Model.USERS> lists = new BLL.USERS().GetList(null, "[USERS_STOP]=0", null);
        DDL_OrderCreater.DataSource = lists;
        DDL_OrderCreater.DataTextField = "Users_Name";
        DDL_OrderCreater.DataValueField = "Users_Code";
        DDL_OrderCreater.DataBind();
/*
        //订单审核人
        DDL_Checker.DataSource = lists;
        DDL_Checker.DataTextField = "Users_Name";
        DDL_Checker.DataValueField = "Users_Code";
        DDL_Checker.DataBind();

        //财务审核人
        DDL_aChecker.DataSource = lists;
        DDL_aChecker.DataTextField = "Users_Name";
        DDL_aChecker.DataValueField = "Users_Code";
        DDL_aChecker.DataBind();

        //发货审核人
        DDL_Sender.DataSource = lists;
        DDL_Sender.DataTextField = "Users_Name";
        DDL_Sender.DataValueField = "Users_Code";
        DDL_Sender.DataBind();

        //结算人
        DDL_Clearer.DataSource = lists;
        DDL_Clearer.DataTextField = "Users_Name";
        DDL_Clearer.DataValueField = "Users_Code";
        DDL_Clearer.DataBind();
*/
        ListItem li = new ListItem();
        li.Value = "-1";
        li.Text = "选择";
        DDL_OrderCreater.Items.Insert(0, li);
       /* DDL_Checker.Items.Insert(0, li);
        DDL_aChecker.Items.Insert(0, li);
        DDL_Sender.Items.Insert(0, li);
        DDL_Clearer.Items.Insert(0, li);*/
    }

    #endregion

    #region 交易类型
    private void ORDER_DTCODE()
    {/*
        List<Model.DEALTYPE> properties = new BLL.DEALTYPE().GetAllList();
        DDL_tradetype.DataSource = properties;
        DDL_tradetype.DataTextField = "DT_NAME";
        DDL_tradetype.DataValueField = "DT_CODE";
        DDL_tradetype.DataBind();
        ListItem li = new ListItem();
        li.Text = "全部";
        li.Value = "-1";
        DDL_tradetype.Items.Insert(0, li);*/
    }
    #endregion

    #region 支付方式初始化
    private void InitPayType()
    {/*
        //绑定支付方式
        List<Model.PAYTYPE> paytype = new List<Model.PAYTYPE>();
        paytype = new BLL.PAYTYPE().GetAllList();
        DDL_PayType.DataSource = paytype;
        DDL_PayType.DataTextField = "PT_NAME";
        DDL_PayType.DataValueField = "PT_CODE";
        DDL_PayType.DataBind();
        ListItem li = new ListItem();
        li.Text = "全部";
        li.Value = "-1";
        DDL_PayType.Items.Insert(0, li);*/
    }
    #endregion

    #region 初始化服务站
    private void Init_ServiceSite()
    {
        int recordCount;
        DataSet dst = new BLL.ServiceSites().GetMain(1, 10000, "and SS_IsDel=0", out recordCount);
        DDL_ServerState.DataSource = dst;
        DDL_ServerState.DataValueField = "SS_ID";
        DDL_ServerState.DataTextField = "SS_Name";
        DDL_ServerState.DataBind();

        ListItem li = new ListItem();
        li.Text = "全部";
        li.Value = "-1";
        DDL_ServerState.Items.Insert(0, li);
    }
    #endregion
    #region 订单意见
    private void Init_Suggestion()
    {/*
        DDL_ORDER_STATETYPECODE.DataSource = new BLL.STATETYPE().GetAllList();
        DDL_ORDER_STATETYPECODE.DataTextField = "ST_NAME";
        DDL_ORDER_STATETYPECODE.DataValueField = "ST_CODE";
        DDL_ORDER_STATETYPECODE.DataBind();

        ListItem li = new ListItem();
        li.Text = "全部";
        li.Value = "-1";
        DDL_ORDER_STATETYPECODE.Items.Insert(0, li);*/
    }
    #endregion
    #region 绑定
    private DataSet GetData()
    {
        string where = " where 1=1";
        where += Tbx_OrderID.Text.Trim() == "" ? "" : " and [ORDERDOC].Order_ID='" + Tbx_OrderID.Text.Trim() + "'";
        where += Tbx_LinkOrder.Text.Trim() == "" ? "" : " and [ORDERDOC].Order_AID='" + Tbx_LinkOrder.Text.Trim() + "'";
        where += DDL_OrderFrom.Value == "" ? "" : " and ORDERDOC.ORDER_OFCODE IN (SELECT * FROM dbo.Split('" + DDL_OrderFrom.Value + "',','))";
        where += DDL_OrderCreater.SelectedValue == "-1" ? "" : " and [ORDERDOC].ORDER_CREATER='" + DDL_OrderCreater.SelectedValue + "'";
        where += Tbx_OrderCreateF.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_CREATEDATE>='" + Tbx_OrderCreateF.Text.Trim() + "'";
        where += Tbx_OrderCreateT.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_CREATEDATE<='" + Tbx_OrderCreateT.Text.Trim() + "'";
        where += Tbx_CustomerCode.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_CUSTOMERCODE in (select * from dbo.Split('" + Tbx_CustomerCode.Text.Trim() + "',','))";
        where += DDL_tradetype.Value == "" ? "" : " and [PAYDETAIL].PD_DTCODE IN (SELECT * FROM dbo.Split('" + DDL_tradetype.Value + "',','))";
        where += DDL_PayType.Value == "" ? "" : " and [PAYDETAIL].PD_PAYTYPE IN (SELECT * FROM dbo.Split('" + DDL_PayType.Value + "',','))";
        where += Tbx_CheckDateF.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_CHECKDATE>='" + Tbx_CheckDateF.Text.Trim() + "'";
        where += Tbx_CheckDateT.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_CHECKDATE<='" + Tbx_CheckDateT.Text.Trim() + "'";
        where += DDL_Check.SelectedValue == "-1" ? "" : " and OrderDoc.ORDER_CHECK=" + DDL_Check.SelectedValue;
        where += DDL_Acheck.SelectedValue == "-1" ? "" : " and OrderDoc.ORDER_ACHECK=" + DDL_Acheck.SelectedValue;
        where += DDL_Send.SelectedValue == "-1" ? "" : " and OrderDoc.ORDER_SEND=" + DDL_Send.SelectedValue;
        where += DDL_Clear.SelectedValue == "-1" ? "" : " and OrderDoc.ORDER_CLEAR=" + DDL_Clear.SelectedValue;
        where += Tbx_SendDateF.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_SENDDATE>='" + Tbx_SendDateF.Text.Trim() + "'";
        where += Tbx_SendDateT.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_SENDDATE<='" + Tbx_SendDateT.Text.Trim() + "'";
        where += DDL_Checker.Value == "" ? "" : " and OrderDoc.ORDER_CHECKER IN (SELECT * FROM dbo.Split('" + DDL_Checker.Value + "',','))";
        where += DDL_aChecker.Value == "" ? "" : " and OrderDoc.ORDER_ACHECKER IN (SELECT * FROM dbo.Split('" + DDL_aChecker.Value + "',','))";
        where += DDL_Sender.Value == "" ? "" : " and OrderDoc.ORDER_SENDER IN (SELECT * FROM dbo.Split('" + DDL_Sender.Value + "',','))";
        where += DDL_Clearer.Value == "" ? "" : " and OrderDoc.ORDER_ACHECKER IN (SELECT * FROM dbo.Split('" + DDL_Clearer.Value + "',','))";
        where += Tbx_AcheckDateF.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_ACHECKDATE>='" + Tbx_AcheckDateF.Text.Trim() + "'";
        where += Tbx_AcheckDateT.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_ACHECKDATE<='" + Tbx_AcheckDateT.Text.Trim() + "'";
        where += Tbx_TamtF.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_AMT>=" + Tbx_TamtF.Text.Trim();
        where += Tbx_TamtT.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_AMT<=" + Tbx_TamtT.Text.Trim();
        where += Tbx_RLF.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_OFFERAMT>=" + Tbx_RLF.Text.Trim();
        where += Tbx_RLT.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_OFFERAMT<=" + Tbx_RLT.Text.Trim();
        where += Tbx_TranCostF.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_TRANCOST>=" + Tbx_TranCostF.Text.Trim();
        where += Tbx_TranCostT.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_TRANCOST<=" + Tbx_TranCostT.Text.Trim();
        where += Tbx_AAmtF.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_AAMT>=" + Tbx_AAmtF.Text.Trim();
        where += Tbx_AAmtT.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_AAMT<=" + Tbx_AAmtT.Text.Trim();
        where += Tbx_ClearDateF.Text.Trim() == "" ? "" : " and OrderDoc.BYZD9>='" + Tbx_ClearDateF.Text.Trim() + "'";
        where += Tbx_ClearDateT.Text.Trim() == "" ? "" : " and OrderDoc.BYZD9<='" + Tbx_ClearDateT.Text.Trim() + "'";
        where += DDL_HDFK.SelectedValue == "-1" ? "" : " and OrderDoc.ORDER_APAY=" + DDL_HDFK.SelectedValue;
        where += DDL_QueHuo.SelectedValue == "-1" ? "" : " and isnull(OrderDoc.BYZD25,0)='" + DDL_QueHuo.SelectedValue + "'";
        where += DDL_ZF.SelectedValue == "-1" ? "" : " and OrderDoc.ORDER_DELETE=" + DDL_ZF.SelectedValue;
        where += Tbx_ZFDateF.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_DELETEDATE>='" + Tbx_ZFDateF.Text.Trim() + "'";
        where += Tbx_ZFDateT.Text.Trim() == "" ? "" : " and OrderDoc.ORDER_DELETEDATE<='" + Tbx_ZFDateT.Text.Trim() + "'";
        where += DDL_ServerState.SelectedValue == "-1" ? "" : "and OrderDoc.BYZD4=" + DDL_ServerState.SelectedValue;
        where += DDL_ORDER_STATETYPECODE.Value == "" ? "" : " and [ORDERDOC].[ORDER_ID] IN(SELECT DISTINCT ON_BILLNO FROM ORDERNOTE WHERE ON_NOTETYPE IN (SELECT * FROM dbo.Split('" + DDL_ORDER_STATETYPECODE.Value + "',',')))";
        
        if (DDL_TuiHuo.SelectedValue == "0")
        {
            where += " and ((ORDERDOC.Order_ID not in (select RD_DOCID from [RETURNDOC])) OR (ORDERDOC.Order_ID in(select RD_DOCID from [RETURNDOC] where RD_DELETE=1)))";
        }
        if (DDL_TuiHuo.SelectedValue == "1")
        {
            where += " and ORDERDOC.Order_ID  in (select RD_DOCID from [RETURNDOC] where RD_DELETE=0)";
        }

        DataSet ds = new BLL.ORDERDOC().GetOrderStatistics(where);
        return ds;
        
    }
    private void Bind()
    {
        DataSet ds = GetData();
        if (ds != null)
        {
            Repeater1.DataSource = ds.Tables[0];
            Repeater1.DataBind();
        }
        else
        {
            Share.Alert(Page, "无统计数据");
        }
    }
    protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Footer)
        {
            Repeater rp = (Repeater)sender;
            DataTable dt = (DataTable)rp.DataSource;

            int num_sum = 0;//订单数量合计
            int sl_sum = 0;     //数量合计
            decimal spje_sum = 0;   //商品金额合计
            decimal wlje_sum = 0;   //物流金额合计
            decimal rlje_sum = 0;   //让利金额合计
            decimal sjje_sum = 0;   //实际金额合计
            foreach (DataRow row in dt.Rows)
            {
                num_sum++;
                sl_sum += int.Parse(row["ORDER_QTY"].ToString(), NumberStyles.Float);
                spje_sum += decimal.Parse(row["ORDER_AMT"].ToString());
                wlje_sum += decimal.Parse(row["ORDER_TRANCOST"].ToString());
                rlje_sum += decimal.Parse(row["ORDER_OFFERAMT"].ToString());
                sjje_sum += decimal.Parse(row["ORDER_AAMT"].ToString());
            }
            Label lbl_Num_SUM =(Label)e.Item.FindControl("lbl_Num_SUM");
            Label lbl_SL_SUM = (Label)e.Item.FindControl("lbl_SL_SUM");
            Label lbl_SPJE_SUM = (Label)e.Item.FindControl("lbl_SPJE_SUM");
            Label lbl_WL_SUM = (Label)e.Item.FindControl("lbl_WL_SUM");
            Label lbl_RL_SUM = (Label)e.Item.FindControl("lbl_RL_SUM");
            Label lbl_SJJE_SUM = (Label)e.Item.FindControl("lbl_SJJE_SUM");

            lbl_Num_SUM.Text = "订单数：" + num_sum.ToString();
            lbl_SL_SUM.Text = sl_sum.ToString();
            lbl_SPJE_SUM.Text = string.Format("{0:F}", spje_sum);
            lbl_WL_SUM.Text = string.Format("{0:F}", wlje_sum);
            lbl_RL_SUM.Text = string.Format("{0:F}", rlje_sum);
            lbl_SJJE_SUM.Text = string.Format("{0:F}", sjje_sum);
        }
    }
    #endregion


    #region 查找
    protected void Btn_stac_Click(object sender, EventArgs e)
    {       
        Bind();
    }
    #endregion

    #region 导出
    protected void Btn_Export_Click(object sender, EventArgs e)
    {
        DataSet ds = GetData();
        //ds.Tables[0].Columns[0].ColumnName = "订单编号";
        //ds.Tables[0].Columns[1].ColumnName = "订单来源";
        //ds.Tables[0].Columns[2].ColumnName = "会员账号";
        //ds.Tables[0].Columns[3].ColumnName = "会员姓名";
        //ds.Tables[0].Columns[4].ColumnName = "会员昵称";
        //ds.Tables[0].Columns[5].ColumnName = "手机号";
        //ds.Tables[0].Columns[6].ColumnName = "制单人";
        //ds.Tables[0].Columns[7].ColumnName = "制单时间";
        //ds.Tables[0].Columns[8].ColumnName = "商品总数";
        //ds.Tables[0].Columns[9].ColumnName = "商品金额";
        //ds.Tables[0].Columns[10].ColumnName = "物流费用";
        //ds.Tables[0].Columns[11].ColumnName = "让利金额";
        //ds.Tables[0].Columns[12].ColumnName = "实际金额";
        //ds.Tables[0].Columns[13].ColumnName = "审核";
        //ds.Tables[0].Columns[14].ColumnName = "财务审核";
        //ds.Tables[0].Columns[15].ColumnName = "发货";
        //ds.Tables[0].Columns[16].ColumnName = "结算";
        //ds.Tables[0].Columns[17].ColumnName = "配送方式";
        //ds.Tables[0].Columns[18].ColumnName = "配送站";
        //ds.Tables[0].Columns[19].ColumnName = "审核人";
        //ds.Tables[0].Columns[20].ColumnName = "审核时间";
        //ds.Tables[0].Columns[21].ColumnName = "财务审核人";
        //ds.Tables[0].Columns[22].ColumnName = "财务审核时间";
        //ds.Tables[0].Columns[23].ColumnName = "发货人";
        //ds.Tables[0].Columns[24].ColumnName = "发货时间";
        //ds.Tables[0].Columns[25].ColumnName = "结算时间";
        //ds.Tables[0].Columns[26].ColumnName = "客户要求";
        //ds.Tables[0].Columns[27].ColumnName = "备注";
        
        //List<int> l=new List<int>();
        //l.Add(0);
        //ExportExcel.CreateExcel(Page,ds, "OrderStatistic.xls", l);
        DataTable2Excel(ds.Tables[0]);
    }
    #endregion
    /// <summary>
    /// DataTable导出Excel
    /// </summary>
    /// <param name="dtData"></param>
    public static void DataTable2Excel(System.Data.DataTable dtData)
    {
        System.Web.UI.WebControls.DataGrid dgExport = null;
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
        System.IO.StringWriter strWriter = null;
        System.Web.UI.HtmlTextWriter htmlWriter = null;

        if (dtData != null)
        {
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
            curContext.Response.Charset = "";

            strWriter = new System.IO.StringWriter();
            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

            dgExport = new System.Web.UI.WebControls.DataGrid();

            dtData.Columns.Add("审核");
            dtData.Columns.Add("财务审核");
            dtData.Columns.Add("发货");
            dtData.Columns.Add("结算");

            dtData.Columns["ORDER_ID"].ColumnName = "订单编号";
            dtData.Columns["ORDERFROM"].ColumnName = "订单来源";
            dtData.Columns["ORDER_CUSTOMERCODE"].ColumnName = "会员账号";

            dtData.Columns["CUSTOMERNAME"].ColumnName = "会员姓名";
            dtData.Columns["CUSTOMERNIKE"].ColumnName = "会员昵称";
            dtData.Columns["PHONE"].ColumnName = "手机号";
            //dtData.Columns["ORDER_CREATER"].ColumnName = "制单人";
            dtData.Columns["ORDER_CREATEDATE"].ColumnName = "制单时间";

            dtData.Columns["ORDER_QTY"].ColumnName = "商品数量";
            dtData.Columns["ORDER_AMT"].ColumnName = "商品金额";
            dtData.Columns["ORDER_TRANCOST"].ColumnName = "物流费用";
            dtData.Columns["ORDER_OFFERAMT"].ColumnName = "让利金额";
            dtData.Columns["ORDER_AAMT"].ColumnName = "实际金额";
            dtData.Columns["SendMethod"].ColumnName = "配送方式";


            dtData.Columns["ServiceName"].ColumnName = "配送站";
            dtData.Columns["ORDER_CHECKER"].ColumnName = "审核人";
            dtData.Columns["ORDER_CHECKDATE"].ColumnName = "审核时间";
            dtData.Columns["ORDER_ACHECKER"].ColumnName = "财务审核人";
            dtData.Columns["ORDER_ACHECKDATE"].ColumnName = "财务审核时间";
            dtData.Columns["ORDER_SENDER"].ColumnName = "发货人";
            dtData.Columns["ORDER_SENDDATE"].ColumnName = "发货时间";
            dtData.Columns["BYZD9"].ColumnName = "结算时间";
            dtData.Columns["BYZD19"].ColumnName = "客户要求";
            dtData.Columns["ORDER_NOTE"].ColumnName = "备注";
            dtData.Columns["ST_NAME"].ColumnName = "订单意见";
  


            for (int i = 0; i < dtData.Rows.Count; i++)
            {
                dtData.Rows[i]["审核"] = dtData.Rows[i]["ORDER_CHECK"].ToString() == "1" ? "已客审" : "未客审";
                dtData.Rows[i]["财务审核"] = dtData.Rows[i]["ORDER_ACHECK"].ToString() == "1" ? "已审核" : "未审核";
                dtData.Rows[i]["结算"] = dtData.Rows[i]["ORDER_CLEAR"].ToString() == "1" ? "已结算" : "未结算";
                dtData.Rows[i]["发货"] = dtData.Rows[i]["ORDER_SEND"].ToString() == "1" ? "已发货" : "未发货";
            }

            ////移除列 
            dtData.Columns.Remove(dtData.Columns["ORDER_CHECK"]);
            dtData.Columns.Remove(dtData.Columns["ORDER_ACHECK"]);
            dtData.Columns.Remove(dtData.Columns["ORDER_CLEAR"]);
            dtData.Columns.Remove(dtData.Columns["ORDER_SEND"]);

            dgExport.DataSource = dtData.DefaultView;
            dgExport.AllowPaging = false;
            dgExport.DataBind();

            dgExport.RenderControl(htmlWriter);
            curContext.Response.Write(strWriter.ToString());
            curContext.Response.End();
        }
    }
}
